#!/bin/bash
# PostgreSQL 诊断脚本 - 快速定位访问问题

set -e

# 日志函数
log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*"; }
warn() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] ⚠️  $*"; }
err() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] ❌ $*" >&2; }

echo "=========================================="
echo "PostgreSQL 诊断工具"
echo "=========================================="
echo ""

# 设置 KUBECONFIG
if [ -f /etc/rancher/k3s/k3s.yaml ]; then
    export KUBECONFIG=/etc/rancher/k3s/k3s.yaml
elif [ -f ~/.kube/config ]; then
    export KUBECONFIG=~/.kube/config
fi

# 1. 检查 Pod 状态
log "步骤 1: 检查 PostgreSQL Pod 状态"
echo "----------------------------------------"
PG_POD=$(kubectl get pod -l app=postgresql -o jsonpath='{.items[0].metadata.name}' 2>/dev/null || echo "")
if [ -z "$PG_POD" ]; then
    err "PostgreSQL Pod 未找到！"
    log "尝试查看所有 Pod:"
    kubectl get pods -A
    exit 1
fi

log "✓ 找到 Pod: $PG_POD"
kubectl get pod $PG_POD -o wide
echo ""

# 2. 检查 Pod 详细状态
log "步骤 2: 检查 Pod 详细状态"
echo "----------------------------------------"
POD_STATUS=$(kubectl get pod $PG_POD -o jsonpath='{.status.phase}')
log "Pod 状态: $POD_STATUS"

if [ "$POD_STATUS" != "Running" ]; then
    warn "Pod 未处于 Running 状态！"
    log "查看 Pod 事件:"
    kubectl describe pod $PG_POD | tail -20
    echo ""
fi

# 3. 检查容器日志
log "步骤 3: 检查 PostgreSQL 日志（最近 20 行）"
echo "----------------------------------------"
kubectl logs $PG_POD --tail=20
echo ""

# 4. 检查 Service
log "步骤 4: 检查 Service 配置"
echo "----------------------------------------"
kubectl get svc -l app=postgresql
echo ""
kubectl describe svc postgresql | grep -A 5 "Endpoints"
echo ""

# 5. 检查 Secret
log "步骤 5: 检查密码配置"
echo "----------------------------------------"
PG_PASSWORD=$(kubectl get secret postgresql-secret -o jsonpath='{.data.postgres-password}' | base64 -d)
log "PostgreSQL 密码: $PG_PASSWORD"
echo ""

# 6. 测试 Pod 内部连接
log "步骤 6: 测试 Pod 内部连接"
echo "----------------------------------------"
log "尝试在 Pod 内部连接数据库..."
if kubectl exec $PG_POD -- psql -U postgres -c "SELECT version();" 2>&1; then
    log "✓ Pod 内部连接成功！"
else
    err "Pod 内部连接失败！"
    log "查看详细错误:"
    kubectl exec $PG_POD -- psql -U postgres -c "SELECT version();" 2>&1 || true
fi
echo ""

# 7. 检查 pg_hba.conf 配置
log "步骤 7: 检查 PostgreSQL 访问控制配置"
echo "----------------------------------------"
log "查看 pg_hba.conf 配置:"
kubectl exec $PG_POD -- cat /var/lib/postgresql/data/pgdata/pg_hba.conf 2>/dev/null || \
    warn "无法读取 pg_hba.conf（可能数据库未初始化）"
echo ""

# 8. 检查监听地址
log "步骤 8: 检查 PostgreSQL 监听地址"
echo "----------------------------------------"
log "查看 postgresql.conf 中的 listen_addresses:"
kubectl exec $PG_POD -- grep "listen_addresses" /etc/postgresql/postgresql.conf 2>/dev/null || \
    warn "无法读取配置文件"
echo ""

# 9. 测试集群内部连接
log "步骤 9: 测试集群内部 Service 连接"
echo "----------------------------------------"
log "使用临时 Pod 测试连接..."
kubectl run -it --rm pg-test-client --image=postgres:15 --restart=Never -- \
    psql -h postgresql -U postgres -c "SELECT version();" 2>&1 || \
    warn "集群内部连接失败"
echo ""

# 10. 测试 NodePort 连接
log "步骤 10: 测试 NodePort 外部访问"
echo "----------------------------------------"
NODE_IP=$(kubectl get nodes -o jsonpath='{.items[0].status.addresses[?(@.type=="InternalIP")].address}')
log "节点 IP: $NODE_IP"
log "NodePort: 30432"
log "尝试从节点连接..."

if command -v psql &> /dev/null; then
    PGPASSWORD=$PG_PASSWORD psql -h $NODE_IP -p 30432 -U postgres -c "SELECT version();" 2>&1 || \
        warn "NodePort 连接失败"
else
    warn "节点上未安装 psql 客户端，跳过外部连接测试"
fi
echo ""

# 11. 检查防火墙
log "步骤 11: 检查防火墙配置"
echo "----------------------------------------"
if command -v firewall-cmd &> /dev/null; then
    log "防火墙状态:"
    firewall-cmd --list-ports | grep 30432 && log "✓ 端口 30432 已开放" || warn "端口 30432 未开放"
else
    warn "firewall-cmd 未安装，跳过防火墙检查"
fi
echo ""

# 12. 网络连通性测试
log "步骤 12: 测试网络连通性"
echo "----------------------------------------"
log "测试 Service DNS 解析:"
kubectl run -it --rm busybox-test --image=busybox --restart=Never -- nslookup postgresql 2>&1 || true
echo ""

# 总结
echo "=========================================="
echo "诊断总结"
echo "=========================================="
log "Pod 名称: $PG_POD"
log "Pod 状态: $POD_STATUS"
log "节点 IP: $NODE_IP"
log "内部访问: postgresql:5432"
log "外部访问: $NODE_IP:30432"
log "用户名: postgres"
log "密码: $PG_PASSWORD"
echo ""
log "如果仍然无法连接，请检查:"
log "1. Pod 日志是否有错误: kubectl logs $PG_POD"
log "2. Pod 事件: kubectl describe pod $PG_POD"
log "3. 数据目录权限: ls -la /data/postgresql"
log "4. 网络策略是否阻止连接"
echo ""

